<?php

namespace App\Http\Controllers\Admin;
use App\Model\Area;
use App\Model\Checkcategory;
use App\Model\Checkentry;
use App\Model\Checknegative;
use App\Model\Comcheck;
use App\Model\Comcheckagain;
use App\Model\Comcheckagaindetail;
use App\Model\Comcheckdetail;
use App\Model\Company;
use App\Model\Selfentry;
use App\Model\Selfs;
use App\Model\Comcheckself;
use App\User;
use Illuminate\Http\Request;

use App\Http\Requests;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
class WorkController extends Controller
{
    /**
     * @param Request $quest
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function reportlist(Request $quest)
    {
        $tree = array();
    	$area_first = array();
    	$area_first = Area::where('name','北宅街道')->first()->toArray();
        $tree = $area_first;
    	$where = array();
        $where[] = array('pid', $area_first['id']);
    	$area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
    	$tree['street'] = $area;
        return view('admin/work/reportlist')
            ->with('tree',$tree);
    }
    /**
     * @param Request $quest
     * @return mixed
     */
    public function reportlistcontent(Request $quest)
    {
        $cityname = '青岛市';
        $area_first = array();
        $area_first = Area::where('name','青岛市')->first()->toArray();
        $parent_path = $area_first['node'].','.$area_first['id'];
        $where = array();
        $where[] = array('node', 'like', $parent_path.'%');
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree = $this->getProvinceTree($area_first, $area);
        $categoryId = $quest->input('categoryId');
        $enterprise = $quest->input('enterprise');
        $checkstatus=$quest->input('checkstatus');
        $city = $quest->input('city');
        $areaid = [];
        if (!empty($quest->input('city'))){
            $pid = $quest->input('city');
            $level = $quest->input('level');
            switch ($level) {
                case 1:
                    $diqu = 'district';
                    break;
                case 2:
                    $diqu = 'street';
                    break;
                case 3:
                    $diqu = 'community';
                    break;
            }
        }else{
            $pid = 370212;
            $diqu = 'street';
            $level = 2;
        }
        if ($level == 4){
            $area = DB::table('area')->where('id','=',$pid)->select(['id','name'])->get();
        }else{
            $area = DB::table('area')->where('pid','=',$pid)->select(['id','name'])->get();
        }
        
        foreach ($area as $item){
            array_push($areaid,$item->id);
        }
        if(!$city) {
            $diquresult = 'street';
            $arearesult = 370212004;
            $city = 370212004;
        } else {
            $diquresult = 'community';
            $arearesult = $city;
        }
        $cityname = Area::find($city)->name;
        $sql  = Company::leftJoin('area','company.'.$diquresult,'area.id')
                    ->leftJoin('comcheck','company.id','comcheck.companyId')
                    ->select('company.id','area.name','comcheck.wordsUrl','comcheck.id as checkId')
                    ->where('company.'.$diquresult,$arearesult);
        if($enterprise) {
            $sql->where('company.enterprise',$enterprise);
        }

        $data = $sql->orderBy('company.id','desc')->get();
        $data = json_decode(json_encode($data, true));
        $comcheckId = [];
        foreach ($data as $v){
            !empty($v->checkId)?array_push($comcheckId,$v->checkId):'';
        }
        $comcheckdetails  = Comcheckdetail::select('id','checkId','status')
                    ->whereIn('checkId',$comcheckId)->orderBy('id','desc')->get();
        $comcheckdetailsdata = json_decode(json_encode($comcheckdetails, true));

        
        $comcheckagain  = Comcheckagaindetail::select('id','checkId')
                    ->whereIn('checkId',$comcheckId)->orderBy('id','desc')->get();
        $comcheckagaindata = json_decode(json_encode($comcheckagain, true));


        $arr = [];
        $dataarr = [];
        $num = 0;
        $checknum = 0;
        $yinhuannum = 0;
        $againnum = 0;
        foreach ($data as $da){
            if ($da->name == $item->name){
                $num += 1;
                if (!empty($da->wordsUrl)){
                    $checknum += 1;
                }
                foreach ($comcheckdetailsdata as $value){
                    if ($value->checkId == $da->checkId &&$value->status == 2){
                        $yinhuannum += 1;
                    }
                }
                foreach ($comcheckagaindata as $value){
                    if ($value->checkId == $da->checkId){
                        $againnum += 1;
                    }
                }
            }
        }
        $unagainnum = $yinhuannum - $againnum;
        if($yinhuannum) {
            $percent = round($againnum/$yinhuannum*100,2)."%";
        } else {
            $percent = '100%';
        }
        
        $arr = ['product'=>$cityname,
                'comnum'=>$num,
                'checknum'=>$checknum,
                'deangernum'=>$yinhuannum,
                'againnum'=>$againnum,
                'unagainnum'=>$unagainnum,
                'percent'=>$percent
            ];
        array_push($dataarr,$arr);
        $name = !empty($quest->input('name'))?$quest->input('name'):'';
        

        if($checkstatus==1){
            // 整改中 有隐患
            $companyCom = Company::with('tagrelations.taginfo')->whereHas('comcheck.Comcheckdetail',function($query){
                $query->where('status',2);
            })->leftJoin('comcheck','company.id','comcheck.companyId')
                    ->select('company.*','comcheck.id as checkId','comcheck.created')
                    ->where('company.comName','like','%'.$name.'%');
        }else if($checkstatus==2){
            // 正常 无隐患
            $companyCom = Company::with('tagrelations.taginfo')->whereDoesntHave('comcheck.Comcheckdetail',function($query){
                $query->where('status',2);
            })->leftJoin('comcheck','company.id','comcheck.companyId')
                    ->select('company.*','comcheck.id as checkId','comcheck.created')
                    ->where('company.comName','like','%'.$name.'%');
        }else{
            $companyCom = Company::with('tagrelations.taginfo')->leftJoin('comcheck','company.id','comcheck.companyId')
                    ->select('company.*','comcheck.id as checkId','comcheck.created')
                    ->where('company.comName','like','%'.$name.'%');
        }
        if($quest->input('checkdate','')){
            $datearr=explode(' - ',$quest->input('checkdate',''));
            $date1=trim($datearr[0]);
            $date2=trim($datearr[1]);
            // dd($date1.' 00:00:00'.'~~~~'.$date2.' 23:59:59');
            $companyCom->where('comcheck.created','<',$date2.' 23:59:59')->where('comcheck.created','>',$date1.' 00:00:00');
        }
        if (!empty($categoryId)){
            $companyCom->where(['company.categoryId'=>$categoryId]);
        }
        if($level == 4){
            $companyCom->whereIn('company.community',$areaid);
        }else{
            $companyCom->whereIn('company.'.$diqu,$areaid);
        }

        $role = Auth::guard('admin')->user()->role;
        if($role == 'normal'){
            $companyCom->where(
                function ($query) {
                    $query->where('company.userId',Auth::guard('admin')->user()->id)
                        ->orWhere('company.enterprise', '=', '1');
                }
            );
        }
        $categorys = Checkcategory::all();
        $companyCom->orderBy('comcheck.created','DESC');
        $companys = $companyCom->paginate(8);
        // dd($companys->toArray());
        $areaid = [];
        foreach ($companys as $k=>$v){
            (in_array($v->province,$areaid)||(empty($v->province)))?'':array_push($areaid,$v->province);
            (in_array($v->city,$areaid)||(empty($v->city)))?'':array_push($areaid,$v->city);
            (in_array($v->district,$areaid)||(empty($v->district)))?'':array_push($areaid,$v->district);
            (in_array($v->street,$areaid)||(empty($v->street)))?'':array_push($areaid,$v->street);
            (in_array($v->community,$areaid)||(empty($v->community)))?'':array_push($areaid,$v->community);
        }
        $area = Area::whereIn('id',$areaid)->get();
        $checkId = [];
        foreach($companys as $com){
            array_push($checkId,$com->checkId);
        }
        $comcheckdetail = Comcheckdetail::whereIn('checkId',$checkId)->get();
        return view('admin/work/reportlistcontent')
            ->with('companys',$companys)
            ->with('tree',$tree)
            ->with('comcheckdetail',$comcheckdetail)
            ->with('name',$name)
            ->with('cityname',$cityname)
            ->with('categoryId',$categoryId)
            ->with('area',$area)
            ->with('dataarr',$dataarr)
            ->with('checkstatus',$checkstatus)
            ->with('categorys',$categorys);
    }

    /**
     * @param Request $quest
     * @return mixed
     */
    public function reportone(Request $quest){
        $tree = array();
    	$area_first = array();
    	$area_first = Area::where('name','北宅街道')->first()->toArray();
        $tree = $area_first;
    	$where = array();
        $where[] = array('pid', $area_first['id']);
    	$area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
    	$tree['street'] = $area;
        $companyId = $quest->input('id');
        $company =  Company::find($companyId);
        $comcheck = Comcheck::leftJoin('users','comcheck.userId','users.id')->select('comcheck.*','users.name')->where(['comcheck.companyId' => $companyId])->get();
        if (empty(json_decode(json_encode($comcheck), true))) {
            echo "<script>alert('该公司还未检查');history.go(-1);</script>";
            exit();
        }
        $comcheckdetail = Comcheckdetail::where(['checkId'=>$comcheck[0]->id])->where(['status'=>2])->get();
        $comcheckModel = new Comcheckdetail();
        $checkdetail = $comcheckModel->getCheckDetail($companyId,$comcheck[0]->id);
        $comcheckdetail = json_decode(json_encode($comcheckdetail), true);
        $is = empty($comcheckdetail)?1:0;
        if (!empty($comcheckdetail)) {        //有异常需要整改
            //获取异常点
            $negatives = [];
            foreach ($comcheckdetail as $k=>$v){
                if (!empty($v['negatives'])){
                    $arr = explode(',',$v['negatives']);
                    foreach ($arr as $ar){
                        if (!empty($ar)){
                            array_push($negatives,$ar);
                        }
                    }
                }
            }
            $negatives = Checknegative::whereIn('id',$negatives)->get();
            $comcheckagain = Comcheckagain::where(['checkId'=>$comcheck[0]->id])->get();
            if (!empty(json_decode(json_encode($comcheckagain), true))){
                $comcheckagainId = $comcheckagain[0]->id;
                $comcheckagaindetail = Comcheckagaindetail::where(['checkId'=>$comcheckagainId])->where(['status'=>1])->get();
                $comcheckagaindetail = json_decode(json_encode($comcheckagaindetail), true);
                $again = empty($comcheckagaindetail)?1:0;
            }else{
                $again = 1;
            }
        }else{
            $again = 1;         //不存在隐患不需要整改整改状态正常
            $negatives = '';
        }
        $negatives = json_decode(json_encode($negatives), true);
        $again = empty($comcheckdetail)?1:0;
        $categoryname = empty($company->categoryId)?'':Checkcategory::find($company->categoryId)->name;
        $address = $company->address;
        $shequ = empty($company->community)?'':Area::find($company->community)->name;
        $doc = '';
        $work = '';
        
        foreach($checkdetail as $k => $v) {
            if($v->status == 1) {
                if(!empty($v->content)) {
                    $doc .= $v->content;
                }
                if(!empty($v->necontent)) {
                    
                    $doc .= $v->necontent.';';
                }
            } 
            if($v->status == 2) {
                if(!empty($v->content)) {
                    $work .= $v->content;
                }
                if(!empty($v->necontent)) {
                    $work .= $v->necontent.';';
                }
            }
        }
        if($doc == '') {
            $doc = '基础档案：无';
        } else {
            $doc = '基础档案：'.$doc;
        }
        if($work == '') {
            $work = '作业现场：无';
        } else {
            $work = '作业现场：'.$work;
        }
        return view('admin/work/reportone')
            ->with('comcheck',$comcheck)
            ->with('tree',$tree)
            ->with('doc',$doc)
            ->with('work',$work)
            ->with('address',$address)
            ->with('categoryname',$categoryname)
            ->with('shequ',$shequ)
            ->with('negatives',$negatives)
            ->with('again',$again)
            ->with('companyId',$companyId)
            ->with('is',$is)
            ->with('company',$company);
    }

    /**
     * @param Request $quest
     * @return mixed
     */
    public function reportones(Request $quest){
        $cityname = '青岛市';
        $area_first = array();
        $area_first = Area::where('name','青岛市')->first()->toArray();
        $parent_path = $area_first['node'].','.$area_first['id'];
        $where = array();
        $where[] = array('node', 'like', $parent_path.'%');
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree = $this->getProvinceTree($area_first, $area);
        $companyId = $quest->input('id');
        $company =  Company::find($companyId);
        $comcheck = Comcheck::leftJoin('users','comcheck.userId','users.id')->select('comcheck.*','users.name')->where(['comcheck.companyId' => $companyId])->get();
        if (empty(json_decode(json_encode($comcheck), true))) {
            echo "<script>alert('该公司还未检查');history.go(-1);</script>";
            exit();
        }
        $comcheckdetail = Comcheckdetail::where(['checkId'=>$comcheck[0]->id])->where(['status'=>2])->get();
        $comcheckdetail = json_decode(json_encode($comcheckdetail), true);
        $is = !empty($comcheckdetail)?1:0;
        if (!empty($comcheckdetail)) {        //有异常需要整改
            //获取异常点
            $negatives = [];
            foreach ($comcheckdetail as $k=>$v){
                if (!empty($v['negatives'])){
                    $arr = explode(',',$v['negatives']);
                    foreach ($arr as $ar){
                        if (!empty($ar)){
                            array_push($negatives,$ar);
                        }
                    }
                }
            }
            $negatives = Checknegative::whereIn('id',$negatives)->get();
            $comcheckagain = Comcheckagain::where(['checkId'=>$comcheck[0]->id])->get();
            if (!empty(json_decode(json_encode($comcheckagain), true))){
                $comcheckagainId = $comcheckagain[0]->id;
                // 未整改的复查项
                $comcheckagaindetail = Comcheckagaindetail::where(['checkId'=>$comcheckagainId])->where(['status'=>1])->get();
                $comcheckagaindetail = json_decode(json_encode($comcheckagaindetail), true);
                $again = empty($comcheckagaindetail)?1:0;
            }else{
                $again = 1;
            }
        }else{
            $again = 1;         //不存在隐患不需要整改整改状态正常
            $negatives = '';
        }
        $negatives = json_decode(json_encode($negatives), true);
        $categoryname = empty($company->categoryId)?'':Checkcategory::find($company->categoryId)->name;
        $address = (empty($company->province)?'':(Area::find($company->province)->name)).(empty($company->city)?'':(Area::find($company->city)->name)).(empty($company->district)?'':(Area::find($company->district)->name)).(empty($company->street)?'':(Area::find($company->street)->name)).(empty($company->community)?'':(Area::find($company->community)->name)).$company->address;
        $shequ = empty($company->community)?'':Area::find($company->community)->name;
        return view('admin/work/reportones')
            ->with('comcheck',$comcheck)
            ->with('tree',$tree)
            ->with('address',$address)
            ->with('categoryname',$categoryname)
            ->with('shequ',$shequ)
            ->with('companyId',$companyId)
            ->with('negatives',$negatives)
            ->with('again',$again)
            ->with('is',$is)
            ->with('company',$company);
    }
    public function reportones_bak20231011(Request $quest){
        $cityname = '青岛市';
        $area_first = array();
        $area_first = Area::where('name','青岛市')->first()->toArray();
        $parent_path = $area_first['node'].','.$area_first['id'];
        $where = array();
        $where[] = array('node', 'like', $parent_path.'%');
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree = $this->getProvinceTree($area_first, $area);
        $companyId = $quest->input('id');
        $company =  Company::find($companyId);
        $comcheck = Comcheck::leftJoin('users','comcheck.userId','users.id')->select('comcheck.*','users.name')->where(['comcheck.companyId' => $companyId])->first();
        if (!$comcheck) {
            echo "<script>alert('该公司还未检查');history.go(-1);</script>";
            exit();
        }
        $comcheckdetail = Comcheckdetail::where('checkId',$comcheck->id)->where('status',2)->get();
        // 是否存在隐患
        $is = count($comcheckdetail)>0?1:0;

        if ($is) {        //有异常需要整改
            //获取异常点
            $negatives = [];
            foreach ($comcheckdetail as $k=>$v){
                if (!empty($v['negatives'])){
                    $arr = explode(',',$v['negatives']);
                    foreach ($arr as $ar){
                        if (!empty($ar)){
                            array_push($negatives,$ar);
                        }
                    }
                }
            }
            $negatives = Checknegative::whereIn('id',$negatives)->get();
            $comcheckagain = Comcheckagain::where('checkId',$comcheck->id)->first();
            if ($comcheckagain){
                // 未整改的复查项
                $comcheckagaindetail = Comcheckagaindetail::where('checkId',$comcheckagain->id)->where('status',1)->get();
                $again = count($comcheckagaindetail)==0?1:0;
            }else{
                $again = 1;
            }
        }else{
            $again = 1;         //不存在隐患不需要整改  整改状态正常
            $negatives = '';
        }
        $negatives = json_decode(json_encode($negatives), true);
        $categoryname = empty($company->categoryId)?'':Checkcategory::find($company->categoryId)->name;
        $address = (empty($company->province)?'':(Area::find($company->province)->name)).(empty($company->city)?'':(Area::find($company->city)->name)).(empty($company->district)?'':(Area::find($company->district)->name)).(empty($company->street)?'':(Area::find($company->street)->name)).(empty($company->community)?'':(Area::find($company->community)->name)).$company->address;
        $shequ = empty($company->community)?'':Area::find($company->community)->name;
        return view('admin/work/reportones')
            ->with('comcheck',$comcheck)
            ->with('tree',$tree)
            ->with('address',$address)
            ->with('categoryname',$categoryname)
            ->with('shequ',$shequ)
            ->with('companyId',$companyId)
            ->with('negatives',$negatives)
            ->with('again',$again)
            ->with('is',$is)
            ->with('company',$company);
    }
    /**
     * @param Request $quest
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function reportexport(Request $quest){
        $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created','comcheck.userId');
        
        $categorys = Checkcategory::all();
        $companys = $companyCom->get();
        $areaid = [];
        foreach ($companys as $k=>$v){
            (in_array($v->province,$areaid)||(empty($v->province)))?'':array_push($areaid,$v->province);
            (in_array($v->city,$areaid)||(empty($v->city)))?'':array_push($areaid,$v->city);
            (in_array($v->district,$areaid)||(empty($v->district)))?'':array_push($areaid,$v->district);
            (in_array($v->street,$areaid)||(empty($v->street)))?'':array_push($areaid,$v->street);
            (in_array($v->community,$areaid)||(empty($v->community)))?'':array_push($areaid,$v->community);
        }
        $area = Area::whereIn('id',$areaid)->get();
        $checkId = [];
        foreach($companys as $com){
            array_push($checkId,$com->checkId);
        }
        $comcheckdetail = Comcheckdetail::whereIn('checkId',$checkId)->where('status','=',2)->get();
        $comcheckagain = Comcheckagain::whereIn('checkId',$checkId)->get();
        //开始写入
        $excel = new \PHPExcel();
        $excel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet = $excel->getActiveSheet();
        $letter = array('A','B','C','D','E','F','G','H','I','J','K','L');
        $objActSheet->setCellValue("A1","北宅街道检查档案统计表");
        $objActSheet->mergeCells('A1:L1');
        $objActSheet->getStyle('A1')->getAlignment()->setWrapText(true);
        $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
        $objActSheet->getStyle('A1')->getFont()->setName('黑体');
        $objActSheet->getStyle('A1')->getFont()->setSize(20);
        $objActSheet->getStyle('A1')->getFont()->setBold(true);
        //part1
        $objActSheet->setCellValue("A2","单位名称");
        $objActSheet->setCellValue("B2","单位地址");
        $objActSheet->setCellValue("C2","单位负责人");
        $objActSheet->setCellValue("D2","联系电话");
        $objActSheet->setCellValue("E2","单位人数");
        $objActSheet->setCellValue("F2","占地面积");
        $objActSheet->setCellValue("G2","检查时间");
        $objActSheet->setCellValue("H2","检查人员");
        $objActSheet->setCellValue("I2","隐患数量");
        $objActSheet->setCellValue("J2","复查时间");
        $objActSheet->setCellValue("K2","复查人员");
        $objActSheet->setCellValue("L2","备注");
        $orirow = 3;
        foreach ($companys as $company){
            $objActSheet->setCellValue("A".$orirow,$company->comName);
            $address = '';
            foreach ($area as $a){
                if ( !empty($company->district)&&$company->district == $a->id){
                    $address .= $a->name;
                }
                if ( !empty($company->street)&&$company->street == $a->id){
                    $address .= $a->name;
                }
                if ( !empty($company->community)&&$company->community == $a->id){
                    $address .= $a->name;
                }
            }
            $address .= $company->address;
            $objActSheet->setCellValue("B".$orirow,$address);
            $objActSheet->setCellValue("C".$orirow,$company->leader);
            $objActSheet->setCellValue("D".$orirow,$company->leaderPhone);
            $objActSheet->setCellValue("E".$orirow,$company->workersNum);
            $objActSheet->setCellValue("F".$orirow,$company->space);
            $objActSheet->setCellValue("G".$orirow,$company->created);
            if (!empty($company->userId)){
                $objActSheet->setCellValue("H".$orirow,User::find($company->userId)->name??'');
            }
            $checknum = 0;
            foreach ($comcheckdetail as $detail){
                if ($detail->checkId == $company->checkId){
                    $checknum += 1;
                }
            }
            $objActSheet->setCellValue("I".$orirow,$checknum);
            $againtime = '';
            $againuser = '';
            foreach ($comcheckagain as $again){
                if ($again->checkId == $company->checkId){
                    $againtime = date('Y-m-d',$again->checkTime);
                    $againuser = User::find($again->userId)->name??'';
                }
            }
            $objActSheet->setCellValue("J".$orirow,$againtime);
            $objActSheet->setCellValue("K".$orirow,$againuser);
            $objActSheet->setCellValue("L".$orirow,'');

            $orirow++;
        }
        //统一设置填充内容左右居中、字号、宽度
        foreach ($letter as $l){
            for ($x = 2;$x<=$orirow;$x++){
                $objActSheet->getStyle($l.$x)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
                $objActSheet->getStyle($l.$x)->getFont()->setSize(10);
            }

        }
        for ($x=1; $x<=($orirow+10); $x++) {
            $objActSheet->getRowDimension($x)->setRowHeight(20);//统一设置行高度

        }
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                ),
            ),
        );
        $objActSheet->getColumnDimension('A')->setWidth(30);
        $objActSheet->getColumnDimension('B')->setWidth(60);
        $objActSheet->getColumnDimension('D')->setWidth(20);
        $objActSheet->getColumnDimension('G')->setWidth(30);
        $objActSheet->getColumnDimension('J')->setWidth(30);

        $objActSheet->getStyle('A1:L'.($orirow+10))->applyFromArray($styleArray);//设置粗边框
        $objActSheet->getRowDimension(1)->setRowHeight(30);//单独设置行高度
        $write = new \PHPExcel_Writer_Excel5($excel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename="'."北宅街道检查档案统计表".'.xls"');
        header("Content-Transfer-Encoding:binary");
        $write->save('php://output');
        exit();
    }

    /**
     * @param Request $request
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function chart(Request $request){

        $cityname = '北宅街道';
        $tree = array();
    	$area_first = array();
    	$area_first = Area::where('name','北宅街道')->first()->toArray();
        $tree = $area_first;
    	$where = array();
        $where[] = array('pid', $area_first['id']);
    	$area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
    	$tree['street'] = $area;
        // $tree = $this->getProvinceTree($area_first, $area);

        return view('admin/work/chart')
            ->with('cityname',$cityname)
            ->with('tree',$tree);
    }

    /**
     * @param Request $request
     * @return mixed
     */
    public function chartcontent(Request $request){
        $area_first = array();
        $area_first = Area::where('name','青岛市')->first()->toArray();
        $parent_path = $area_first['node'].','.$area_first['id'];
        $where = array();
        $where[] = array('node', 'like', $parent_path.'%');
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree = $this->getProvinceTree($area_first, $area);
        $category = DB::table('checkcategory')->select(['id','name'])->get();
        $cateArray = [];
        foreach($category as $k => $v)
        {
            $cateArray[] = $v->name;
        }
        array_push($cateArray,'其它');
        $areaid = [];
        if (!empty($request->input('city'))){
            $pid = $request->input('city');
            $level = $request->input('level');
            switch ($level) {
                case 1:
                    $diqu = 'district';
                    break;
                case 2:
                    $diqu = 'street';
                    break;
                case 3:
                    $diqu = 'community';
                    break;
            }
        }else{
            $pid = 370212;
            $diqu = 'street';
            $level = 3;
        }
        if ($level == 4){
            $area = DB::table('area')->where('id','=',$pid)->select(['id','name'])->get();
        }else{
            $area = DB::table('area')->where('pid','=',$pid)->select(['id','name'])->get();
        }
        $cityname = Area::find($pid)->name;
        foreach ($area as $item){
            array_push($areaid,$item->id);
        }
        if ($level == 4){
            $data  = Company::leftJoin('checkcategory','company.categoryId','checkcategory.id')->leftJoin('area','company.community','area.id')->select('company.id','checkcategory.id as categoryId','area.name')->whereIn('company.community',$areaid)->orderBy('company.id','desc')->get();
        }else{
            $data  = Company::leftJoin('checkcategory','company.categoryId','checkcategory.id')->leftJoin('area','company.'.$diqu,'area.id')->select('company.id','checkcategory.id as categoryId','area.name')->whereIn('company.'.$diqu,$areaid)->orderBy('company.id','desc')->get();

        }
        $data = json_decode(json_encode($data, true));
        $dataarr = [];
        foreach ($area as $item){
            if($item->id < 370212004) {
                continue;
            }
            $arr = [];
            $a = 0;$b=0;$c=0;$d=0;$e=0;$f=0;$g=0;$h=0;$i=0;$j=0;$k=0;
            foreach ($data as $da){
                if ($da->name == $item->name){
                    $da->categoryId == 1?$a+=1:'';
                    $da->categoryId == 2?$b+=1:'';
                    $da->categoryId == 3?$c+=1:'';
                    $da->categoryId == 4?$d+=1:'';
                    $da->categoryId == 5?$e+=1:'';
                    $da->categoryId == 6?$f+=1:'';
                    $da->categoryId == 7?$g+=1:'';
                    $da->categoryId == 8?$h+=1:'';
                    $da->categoryId == 9?$i+=1:'';
                    $da->categoryId == 10?$j+=1:'';
                    $da->categoryId >=11?$k+=1:'';
                }
            }
            $arr = ['product'=>$item->name,'餐饮'=>$a,'旅馆'=>$b,'商场'=>$c,'公共娱乐'=>$d,'医疗场所'=>$e,'休闲健身'=>$f,'学校'=>$g,'烟花爆竹'=>$h,'4s店'=>$i,'企业'=>$j,'其它'=>$k,];
            array_push($dataarr,$arr);
        }
        $cateCount = DB::select("select count(company.id) as count,checkcategory.name from checkcategory
                                 left join company on checkcategory.id = company.category group by checkcategory.id ");
        array_unshift($cateArray,'product');
//        dd(json_encode($cateArray,JSON_UNESCAPED_UNICODE));
        return view('admin/work/chartcontent')
            ->with('cate',json_encode($cateArray,JSON_UNESCAPED_UNICODE))
            ->with('category',$category)
            ->with('cityname','北宅街道')
            ->with('tree',$tree)
            ->with('dataarr',$dataarr)
            ->with('data',json_encode($dataarr,JSON_UNESCAPED_UNICODE));
    }

    /**
     * @param Request $request
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function checkchart(Request $request){
        $cityname = '北宅街道';
        $tree = array();
    	$area_first = array();
    	$area_first = Area::where('name','北宅街道')->first()->toArray();
        $tree = $area_first;
    	$where = array();
        $where[] = array('pid', $area_first['id']);
    	$area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
    	$tree['street'] = $area;
        // $tree = $this->getProvinceTree($area_first, $area);

        return view('admin/work/checkchart')
            ->with('cityname',$cityname)
            ->with('tree',$tree);
    }

    /**
     * @param Request $request
     * @return mixed
     */
    public function checkchartcontent(Request $request){
        $area_first = array();
        $area_first = Area::where('name','青岛市')->first()->toArray();
        $parent_path = $area_first['node'].','.$area_first['id'];
        $where = array();
        $where[] = array('node', 'like', $parent_path.'%');
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree = $this->getProvinceTree($area_first, $area);
        $areaid = [];
        if (!empty($request->input('year'))) {
            $year = $request->input('year');
        }else{
            $year = date('Y',time());
        }
        if (!empty($request->input('city'))){
            $pid = $request->input('city');
            $level = $request->input('level');
            switch ($level) {
                case 1:
                    $diqu = 'district';
                    break;
                case 2:
                    $diqu = 'street';
                    break;
                case 3:
                    $diqu = 'community';
                    break;
            }
        }else{
            $pid = 370212004;
            $diqu = 'street';
            $level = 2;
        }
        if ($level == 4){
            $area = DB::table('area')->where('id','=',$pid)->select(['id','name'])->get();
            $diqu = 'community';
        }else{
            $area = DB::table('area')->where('pid','=',$pid)->select(['id','name'])->get();
            $diqu = 'community';
        }
        $cityname = Area::find($pid)->name;
        foreach ($area as $item){
            array_push($areaid,$item->id);
        }
        
        $data  = Company::leftJoin('area','company.'.$diqu,'area.id')->leftJoin('comcheck','company.id','comcheck.companyId')->select('company.id','area.name','comcheck.wordsUrl','comcheck.id as checkId')->whereIn('company.'.$diqu,$areaid)->orderBy('company.id','desc')->get();
        $data = json_decode(json_encode($data, true));
        $comcheckId = [];
        foreach ($data as $v){
            !empty($v->checkId)?array_push($comcheckId,$v->checkId):'';
        }
        $comcheckdetails  = Comcheckdetail::select('id','checkId','status')->whereIn('checkId',$comcheckId)->orderBy('id','desc')->get();
        $comcheckdetailsdata = json_decode(json_encode($comcheckdetails, true));

        $comcheckagain  = Comcheckagain::select('id','checkId')->whereIn('checkId',$comcheckId)->orderBy('id','desc')->get();
        $comcheckagaindata = json_decode(json_encode($comcheckagain, true));

        $dataarr = [];
        foreach ($area as $item){
            $arr = [];
            $num = 0;
            $checknum = 0;
            $yinhuannum = 0;
            $againnum = 0;
            foreach ($data as $da){
                if ($da->name == $item->name){
                    $num += 1;
                    if (!empty($da->wordsUrl)){
                        $checknum += 1;
                    }
                    foreach ($comcheckdetailsdata as $value){
                        if ($value->checkId == $da->checkId &&$value->status == 2){
                            $yinhuannum += 1;
                        }
                    }
                    foreach ($comcheckagaindata as $value){
                        if ($value->checkId == $da->checkId){
                            $againnum += 1;
                        }
                    }
                }
            }
            $unagainnum = $yinhuannum - $againnum;
            if($yinhuannum) {
                $percent = round($againnum/$yinhuannum*100,2)."%";
            } else {
                $percent = '100%';
            }
            
            $arr = ['product'=>$item->name,
                    'comnum'=>$num,
                    'checknum'=>$checknum,
                    'deangernum'=>$yinhuannum,
                    'againnum'=>$againnum,
                    'unagainnum'=>$unagainnum,
                    'percent'=>$percent
                ];
            array_push($dataarr,$arr);
        }
        
        $data1  = Company::leftJoin('area','company.'.$diqu,'area.id')->leftJoin('comcheck','company.id','comcheck.companyId')->select('company.id','area.name','comcheck.wordsUrl','comcheck.id as checkId')->whereIn('company.'.$diqu,$areaid)->orderBy('company.id','desc')->get();
        $data1 = json_decode(json_encode($data1, true));
        $comcheckId = [];
        foreach ($data1 as $v){
            !empty($v->checkId)?array_push($comcheckId,$v->checkId):'';
        }
        $comcheckdetails  = Comcheckdetail::leftJoin('checkgroup','comcheckdetail.groupId','checkgroup.id')->select('comcheckdetail.id','checkgroup.status as groupstatus','checkgroup.type')->whereIn('comcheckdetail.checkId',$comcheckId)->where(['comcheckdetail.status'=>2])->orderBy('comcheckdetail.id','desc')->get();
        $comcheckdetailsdata = json_decode(json_encode($comcheckdetails, true));

        $comcheckagain  = Comcheckagain::select('id','checkId')->whereIn('checkId',$comcheckId)->orderBy('id','desc')->get();
        $comcheckagaindata = json_decode(json_encode($comcheckagain, true));
        $againId = [];
        foreach ($comcheckagain as $item){
            array_push($againId,$item->id);
        }

        $comcheckagaindetails  = Comcheckagaindetail::leftJoin('checkgroup','comcheckagaindetail.groupId','checkgroup.id')->select('comcheckagaindetail.id','comcheckagaindetail.status','checkgroup.type')->whereIn('comcheckagaindetail.checkId',$againId)->orderBy('comcheckagaindetail.id','desc')->get();
        $comcheckagaindetailsdata = json_decode(json_encode($comcheckagaindetails, true));
        //事故隐患统计图
        
        return view('admin/work/checkchartcontent')
            ->with('diqu',$diqu)
            ->with('dataarr',$dataarr)
            ->with('tree',$tree)
            ->with('cityname','北宅街道')
            ->with('data',json_encode($dataarr,JSON_UNESCAPED_UNICODE));
    }

    /**
     * @return mixed
     */
    public function firstcheckchart(){
        $areaid = [];
        $status = [
            [
                'status' => 1,
                'title'=>'人的不安全行为'
            ],
            [
                'status' => 2,
                'title'=>'物的不安全状态'
            ],
            [
                'status' => 3,
                'title'=>'作业环境的缺陷'
            ],
            [
                'status' => 4,
                'title'=>'安全管理的缺陷'
            ],
        ];
        $area = DB::table('area')->where('pid','=','370200')->select(['id','name'])->get();
        $diqu = Area::find(370200)->name;
        foreach ($area as $item){
            array_push($areaid,$item->id);
        }
        $data  = Company::leftJoin('area','company.district','area.id')->leftJoin('comcheck','company.id','comcheck.companyId')->select('company.id','area.name','comcheck.wordsUrl','comcheck.id as checkId')->whereIn('company.district',$areaid)->orderBy('company.id','desc')->get();
        $data = json_decode(json_encode($data, true));
        $comcheckId = [];
        foreach ($data as $v){
            !empty($v->checkId)?array_push($comcheckId,$v->checkId):'';
        }
        $comcheckdetails  = Comcheckdetail::leftJoin('checkgroup','comcheckdetail.groupId','checkgroup.id')->select('comcheckdetail.id','checkgroup.status as groupstatus','checkgroup.type')->whereIn('comcheckdetail.checkId',$comcheckId)->where(['comcheckdetail.status'=>2])->orderBy('comcheckdetail.id','desc')->get();
        $comcheckdetailsdata = json_decode(json_encode($comcheckdetails, true));

        $comcheckagain  = Comcheckagain::select('id','checkId')->whereIn('checkId',$comcheckId)->orderBy('id','desc')->get();
        $comcheckagaindata = json_decode(json_encode($comcheckagain, true));
        $againId = [];
        foreach ($comcheckagain as $item){
            array_push($againId,$item->id);
        }

        $comcheckagaindetails  = Comcheckagaindetail::leftJoin('checkgroup','comcheckagaindetail.groupId','checkgroup.id')->select('comcheckagaindetail.id','comcheckagaindetail.status','checkgroup.type')->whereIn('comcheckagaindetail.checkId',$againId)->where(['comcheckagaindetail.status'=>2])->orderBy('comcheckagaindetail.id','desc')->get();
        $comcheckagaindetailsdata = json_decode(json_encode($comcheckagaindetails, true));
        //事故隐患统计图
        $dataarr = [];
        foreach ($status as $item){
            $arr = [];
            $xiaofang = 0;
            $shengchan = 0;
            foreach ($comcheckdetailsdata as $value){
                if ($value->groupstatus == $item['status']){
                    if ($value->type == 1){
                        $xiaofang += 1;
                    }
                    if ($value->type == 2){
                        $shengchan += 1;
                    }
                }
            }
            $arr = ['product'=>$item['title'],'消防安全隐患'=>$xiaofang,'生产安全隐患'=>$shengchan];
            array_push($dataarr,$arr);
        }
        //隐患整改情况统计图
        $all = 0;
        $allxf = 0;
        $allsc = 0;
        $yizhenggai = 0;
        $yizhenggaixf = 0;
        $yizhenggaisc = 0;
        $bufenzhenggai = 0;
        $bufenzhenggaixf = 0;
        $bufenzhenggaisc = 0;
        foreach ($comcheckagaindetailsdata as $value){
            if ($value->type == 1){
                if ($value->status == 2){
                    $bufenzhenggai += 1;
                    $bufenzhenggaixf += 1;
                }
                if ($value->status == 3){
                    $yizhenggai += 1;
                    $yizhenggaixf += 1;
                }
            }
            if ($value->type == 2){
                if ($value->status == 2){
                    $bufenzhenggai += 1;
                    $bufenzhenggaisc += 1;
                }
                if ($value->status == 3){
                    $yizhenggai += 1;
                    $yizhenggaisc += 1;
                }
            }
        }
        foreach ($dataarr as $v){
            $all += $v['消防安全隐患'];
            $allxf += $v['消防安全隐患'];
            $all += $v['生产安全隐患'];
            $allsc += $v['生产安全隐患'];
        }
        $zhenggai = [
            ['value'=>$yizhenggai,'name'=>'已整改','itemStyle'=>['color'=>'#1acd7e']],
            ['value'=>$bufenzhenggai,'name'=>'部分整改'],
            ['value'=>($all-$yizhenggai-$bufenzhenggai),'name'=>'未整改'],
        ];
        $zhenggaixf = [
            ['value'=>$yizhenggaixf,'name'=>'已整改','itemStyle'=>['color'=>'#1acd7e']],
            ['value'=>$bufenzhenggaixf,'name'=>'部分整改'],
            ['value'=>($allxf-$yizhenggaixf-$bufenzhenggaixf),'name'=>'未整改'],
        ];
        $zhenggaisc = [
            ['value'=>$yizhenggaisc,'name'=>'已整改','itemStyle'=>['color'=>'#1acd7e']],
            ['value'=>$bufenzhenggaisc,'name'=>'部分整改',],
            ['value'=>($allsc-$yizhenggaisc-$bufenzhenggaisc),'name'=>'未整改'],
        ];
        return view('admin/work/firstcheckchart')
            ->with('dataarr',$dataarr)
            ->with('diqu',$diqu)
            ->with('zhenggai',json_encode($zhenggai,JSON_UNESCAPED_UNICODE))
            ->with('zhenggaixf',json_encode($zhenggaixf,JSON_UNESCAPED_UNICODE))
            ->with('zhenggaisc',json_encode($zhenggaisc,JSON_UNESCAPED_UNICODE))
            ->with('data',json_encode($dataarr,JSON_UNESCAPED_UNICODE));
    }

    public function test(){
        return view('admin/work/test');

    }

    /**
     * @param $area_first
     * @param $area
     * @return mixed
     */
    protected function getProvinceTree($area_first,$area)
    {
        $area_tree = $area_first;
        $district_street = array();
        $community = array();
        foreach($area as $k=>$v) {
            if($v['level']!=5) {
                $district_street[] = $v;
            } else {
                $community[] = $v;
            }
        }
        $district_street = array_column($district_street, null, 'id');
        $community_arr = array();
        foreach ($community as $key => $value) {
            $community_arr[$value['pid']][] = $value;
        }
        /*获取市的各区*/
        foreach($district_street as $k=>$v) {
            if($v['level']==3 && $v['name'] == '崂山区') {
                $area_tree['district'][] = $v;
            }
        }
        /*获取街道*/
        foreach ($area_tree['district'] as $key => $value) {
            foreach($district_street as $k=>$v) {
                if($value['id']==$v['pid']) {
                    $area_tree['district'][$key]['street'][] = $v;
                }
            }
        }
        /*获取社区*/
        foreach($area_tree['district'] as $k=>$v) {
            foreach($v['street'] as $key=>$value) {
                if(isset($community_arr[$value['id']])) {
                    $area_tree['district'][$k]['street'][$key]['community'] = $community_arr[$value['id']];
                } else {
                    $area_tree['district'][$k]['street'][$key]['community'] = array();
                }
            }
        }
        return $area_tree;
    }

    /**
     * @param Request $quest
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function checktimes(Request $quest)
    {
        $cityname = '北宅街道';
        $tree = array();
    	$area_first = array();
    	$area_first = Area::where('name','北宅街道')->first()->toArray();
        $tree = $area_first;
    	$where = array();
        $where[] = array('pid', $area_first['id']);
    	$area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
    	$tree['street'] = $area;
        // $tree = $this->getProvinceTree($area_first, $area);

        return view('admin/work/checktimes')
            ->with('cityname',$cityname)
            ->with('tree',$tree);
    }

    /**
     * @param Request $quest
     * @return mixed
     */
    public function checktimescontent(Request $request)
    {
        $area_first = array();
        $area_first = Area::where('name','青岛市')->first()->toArray();
        $parent_path = $area_first['node'].','.$area_first['id'];
        $where = array();
        $where[] = array('node', 'like', $parent_path.'%');
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree = $this->getProvinceTree($area_first, $area);
        $areaid = [];
        if (!empty($request->input('year'))) {
            $year = $request->input('year');
        }else{
            $year = date('Y',time());
        }
        if (!empty($request->input('city'))){
            $pid = $request->input('city');
            $level = $request->input('level');
            switch ($level) {
                case 1:
                    $diqu = 'district';
                    break;
                case 2:
                    $diqu = 'street';
                    break;
                case 3:
                    $diqu = 'community';
                    break;
            }
        }else{
            $pid = 370212004;
            $diqu = 'street';
            $level = 2;
        }
        if ($level == 4){
            $area = DB::table('area')->where('id','=',$pid)->select(['id','name'])->get();
            $diqu = 'community';
        }else{
            $area = DB::table('area')->where('pid','=',$pid)->select(['id','name'])->get();
            $diqu = 'community';
        }
        $cityname = Area::find($pid)->name;
        foreach ($area as $item){
            array_push($areaid,$item->id);
        }
        $data  = Company::leftJoin('area','company.'.$diqu,'area.id')->leftJoin('comcheck','company.id','comcheck.companyId')->select('company.id','area.name','comcheck.wordsUrl','comcheck.id as checkId')->whereIn('company.'.$diqu,$areaid)->where('comcheck.created','like','%'.$year.'%')->orderBy('company.id','desc')->get();
        $data = json_decode(json_encode($data, true));
        $comcheckId = [];
        foreach ($data as $v){
            !empty($v->checkId)?array_push($comcheckId,$v->checkId):'';
        }
        $comcheckdetails  = Comcheckdetail::select('id','checkId','status')->whereIn('checkId',$comcheckId)->orderBy('id','desc')->get();
        $comcheckdetailsdata = json_decode(json_encode($comcheckdetails, true));

        $comcheckagain  = Comcheckagain::select('id','checkId')->whereIn('checkId',$comcheckId)->orderBy('id','desc')->get();
        $comcheckagaindata = json_decode(json_encode($comcheckagain, true));

        $dataarr = [];
        foreach ($area as $item){
            $arr = [];
            $num = 0;
            $checknum = 0;
            $yinhuannum = 0;
            $againnum = 0;
            foreach ($data as $da){
                if ($da->name == $item->name){
                    $num += 1;
                    if (!empty($da->wordsUrl)){
                        $checknum += 1;
                    }
                    foreach ($comcheckdetailsdata as $value){
                        if ($value->checkId == $da->checkId &&$value->status == 2){
                            $yinhuannum += 1;
                        }
                    }
                    foreach ($comcheckagaindata as $value){
                        if ($value->checkId == $da->checkId){
                            $againnum += 1;
                        }
                    }
                }
            }
            $arr = ['product'=>$item->name,'住户监管数量'=>$num,'实际检查数量'=>$checknum,'检查存在隐患数量'=>$yinhuannum,'复查记录数量'=>$againnum];
            array_push($dataarr,$arr);
        }
        
        return view('admin/work/checktimescontent')
            ->with('diqu',$diqu)

            ->with('dataarr',$dataarr)
            ->with('tree',$tree)
            ->with('cityname','北宅街道')
            ->with('data',json_encode($dataarr,JSON_UNESCAPED_UNICODE));

    }

    /**
     * @param Request $quest
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function checktimesreport(Request $quest){
        $categoryId = $quest->input('categoryId');
        $areaid = [];
        if (!empty($quest->input('year'))) {
            $year = $quest->input('year');
        }else{
            $year = date('Y',time());
        }
        if (!empty($quest->input('city'))){
            $pid = $quest->input('city');
            $level = $quest->input('level');
            switch ($level) {
                case 1:
                    $diqu = 'district';
                    break;
                case 2:
                    $diqu = 'street';
                    break;
                case 3:
                    $diqu = 'community';
                    break;
            }
        }else{
            $pid = 370214;
            $diqu = 'street';
            $level = 2;
        }
        if ($level == 4){
            $area = DB::table('area')->where('id','=',$pid)->select(['id','name'])->get();
        }else{
            $area = DB::table('area')->where('pid','=',$pid)->select(['id','name'])->get();
        }
        foreach ($area as $item){
            array_push($areaid,$item->id);
        }
        if (!empty($categoryId)){
            if ($level == 4){
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created')->where(['company.categoryId'=>$categoryId])->where('comcheck.created','like','%'.$year.'%')->whereIn('company.community',$areaid);
            }else{
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created')->where(['company.categoryId'=>$categoryId])->where('comcheck.created','like','%'.$year.'%')->whereIn('company.'.$diqu,$areaid);
            }
        }else{
            if ($level == 4){
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created')->where('comcheck.created','like','%'.$year.'%')->whereIn('company.community',$areaid);
            }else{
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created')->where('comcheck.created','like','%'.$year.'%')->whereIn('company.'.$diqu,$areaid);
            }
        }
        $companys = $companyCom->get();
        $checkId = [];
        foreach($companys as $com){
            array_push($checkId,$com->checkId);
        }
        $comcheckdetail = Comcheckdetail::whereIn('checkId',$checkId)->get();
        $comcheckagian = Comcheckagain::whereIn('checkId',$checkId)->get();
        //开始写入
        $excel = new \PHPExcel();
        $excel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet = $excel->getActiveSheet();
        $letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M');
        $objActSheet->setCellValue("A1","九场所检查企业档案统计表");
        $objActSheet->mergeCells('A1:M1');
        $objActSheet->getStyle('A1')->getAlignment()->setWrapText(true);
        $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
        $objActSheet->getStyle('A1')->getFont()->setName('黑体');
        $objActSheet->getStyle('A1')->getFont()->setSize(20);
        $objActSheet->getStyle('A1')->getFont()->setBold(true);
        //part1
        $objActSheet->setCellValue("A2","序号");
        $objActSheet->setCellValue("B2","企业名称");
        $objActSheet->setCellValue("C2","初查");
        $objActSheet->setCellValue("D2","复查");
        $objActSheet->setCellValue("E2","二次检查");
        $objActSheet->setCellValue("F2","二次复查");
        $objActSheet->setCellValue("G2","三次检查");
        $objActSheet->setCellValue("H2","三次复查");
        $objActSheet->setCellValue("I2","四次检查");
        $objActSheet->setCellValue("J2","四次复查");
        $objActSheet->setCellValue("K2","五次检查");
        $objActSheet->setCellValue("L2","五次复查");
        $objActSheet->setCellValue("M2","总检查次数");
        $orirow = 3;
        $i = 1;
        foreach ($companys as $company){
            $objActSheet->setCellValue("A".$orirow,$i);
            $objActSheet->setCellValue("B".$orirow,$company->comName);
            $set = 0;$setagian = 0;
            foreach($comcheckdetail as $com) {if($com->checkId == $company->checkId){ $set = 1;} }
            foreach($comcheckagian as $again) {if($again->checkId == $company->checkId) {$setagian = 1;}}
            $objActSheet->setCellValue("C".$orirow,$set?1:0);
            $objActSheet->setCellValue("D".$orirow,$setagian?1:0);
            $objActSheet->setCellValue("E".$orirow,0);
            $objActSheet->setCellValue("F".$orirow,0);
            $objActSheet->setCellValue("G".$orirow,0);
            $objActSheet->setCellValue("H".$orirow,0);
            $objActSheet->setCellValue("I".$orirow,0);
            $objActSheet->setCellValue("J".$orirow,0);
            $objActSheet->setCellValue("K".$orirow,0);
            $objActSheet->setCellValue("L".$orirow,0);
            $objActSheet->setCellValue("M".$orirow,$setagian+$set);
            $orirow++;
            $i++;
        }
        //统一设置填充内容左右居中、字号、宽度
        foreach ($letter as $l){
            for ($x = 2;$x<=$orirow;$x++){
                $objActSheet->getStyle($l.$x)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
                $objActSheet->getStyle($l.$x)->getFont()->setSize(10);
            }

        }
        for ($x=1; $x<=($orirow+10); $x++) {
            $objActSheet->getRowDimension($x)->setRowHeight(20);//统一设置行高度

        }
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                ),
            ),
        );
        $objActSheet->getColumnDimension('B')->setWidth(30);

        $objActSheet->getStyle('A1:M'.($orirow+10))->applyFromArray($styleArray);//设置粗边框
        $objActSheet->getRowDimension(1)->setRowHeight(30);//单独设置行高度
        $write = new \PHPExcel_Writer_Excel5($excel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename="'.''."九场所企业检查统计".'.xls"');
        header("Content-Transfer-Encoding:binary");
        $write->save('php://output');
        exit();
    }

    public function detailexport(Request $quest){
//        echo phpinfo();exit();
//        $checkId = $quest->input('checkId');
        $companyId = $quest->input('id');
//        $companyId = 19;
        $comName = Company::find($companyId)->comName;
        $checkId = Comcheck::where(['companyId'=>$companyId])->get()[0]->id;
        $data = Comcheckdetail::where(['checkId'=>$checkId])->get();
        $negatives = [];
        $groupId = [];
        $entryId = [];
        foreach ($data as $v){
            $arr = explode(",",$v->negatives);
            foreach ($arr as $v1){
                empty($v1)?'':array_push($negatives,$v1);
            }
            array_push($groupId,$v->groupId);
            array_push($entryId,$v->entryId);
        }
        $negatives = Checknegative::whereIn('id',$negatives)->get();
        $negativesall = Checknegative::whereIn('entryId',$entryId)->get();
//        $groupInfo = Checkgroup::find($groupId);
//        if(empty($groupInfo)){
//            return back();
//        }
//        $comcheckgroup = [];
//        $comcheckgroup = Comcheckgroup::where(['checkId'=>$checkId,'groupId'=>$groupId])->first();
        $entrys = Checkentry::whereIn('groupId',$groupId)->get();
        //开始写入
        $excel = new \PHPExcel();
        $excel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet = $excel->getActiveSheet();
        $letter = array('A','B','C','D','E');
        $objActSheet->setCellValue("A1",$comName."现场检查记录表");
        $objActSheet->mergeCells('A1:E1');
        $objActSheet->getStyle('A1')->getAlignment()->setWrapText(true);
        $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
        $objActSheet->getStyle('A1')->getFont()->setName('黑体');
        $objActSheet->getStyle('A1')->getFont()->setSize(20);
        $objActSheet->getStyle('A1')->getFont()->setBold(true);
        //part1
        $objActSheet->setCellValue("A2","序号");
        $objActSheet->setCellValue("B2","检查内容");
        $objActSheet->setCellValue("C2","检查方法");
        $objActSheet->setCellValue("D2","检查结果");
        $objActSheet->setCellValue("E2","取证资料");
        $orirow = 3;
        $i=1;
        for ($x=1; $x<=(1000); $x++) {
            $objActSheet->getRowDimension($x)->setRowHeight(20);//统一设置行高度

        }
        foreach ($entrys as $entry){
            foreach ($data as $da){
                if($da->entryId == $entry->id){
                    $objActSheet->setCellValue("A".$orirow,$i);
                    $objActSheet->setCellValue("B".$orirow,$entry->entry);
                    $i++;
                    if (!empty($da->pictures)){
                        $pic = explode(',',$da->pictures);
                        foreach ($pic as $ve){
                            if (!empty($ve)){
                                $img=new \PHPExcel_Worksheet_Drawing();
                                $img->setPath(str_replace('\\', '/', base_path()).'/public'.$ve);//写入图片路径
                                $img->setHeight(300);//写入图片高度
                                $img->setWidth(300);//写入图片宽度
                                $objActSheet->getRowDimension($orirow)->setRowHeight(130);//统一设置行高度

                                $img->setOffsetX(1);//写入图片在指定格中的X坐标值
                                $img->setOffsetY(1);//写入图片在指定格中的Y坐标值
                                $img->setRotation(1);//设置旋转角度
                                $img->getShadow()->setVisible(true);//
                                $img->getShadow()->setDirection(50);//
                                $img->setCoordinates("E".$orirow);//设置图片所在表格位置
                                $img->setWorksheet($excel->getActiveSheet());//把图片写到当前的表格中
                            }
                        }
                    }
                    foreach ($negativesall as $ne){
                        if ($da->entryId == $ne->entryId){
                            $objActSheet->setCellValue("C".$orirow,$ne->content);
                            $objActSheet->setCellValue("D".$orirow,'合格');
                            $arr = explode(',',$da->negatives);
                            foreach ($arr as $ar) {
                                if ($ne->id == $ar && !empty($ne)){
                                    $objActSheet->setCellValue("D".$orirow,'不合格');
                                    $phpColor = new \PHPExcel_Style_Color();
                                    $phpColor->setRGB('FF0000');
                                    $excel->getActiveSheet()->getStyle("D".$orirow)->getFont()->setColor( $phpColor );

                                }
                            }
                            $orirow++;
                        }
                    }
                }
            }
        }
        $orirow++;

        //统一设置填充内容左右居中、字号、宽度
        foreach ($letter as $l){
            for ($x = 2;$x<=$orirow;$x++){
                $objActSheet->getStyle($l.$x)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
                $objActSheet->getStyle($l.$x)->getFont()->setSize(10);
            }

        }

        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                ),
            ),
        );
        $objActSheet->getColumnDimension('A')->setWidth(10);
        $objActSheet->getColumnDimension('B')->setWidth(90);
        $objActSheet->getColumnDimension('C')->setWidth(60);
        $objActSheet->getColumnDimension('D')->setWidth(20);
        $objActSheet->getColumnDimension('E')->setWidth(50);

        $objActSheet->getStyle('A1:E'.($orirow+10))->applyFromArray($styleArray);//设置粗边框
        $objActSheet->getRowDimension(1)->setRowHeight(30);//单独设置行高度
        $write = new \PHPExcel_Writer_Excel5($excel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename="'.$comName.''."现场检查记录表".'.xls"');
        header("Content-Transfer-Encoding:binary");
        $write->save('php://output');
        exit();
    }

    /*整改*/
    public function reform($checkId) {
        /*获取初查*/
        $check = Comcheck::findOrFail($checkId);
        $check_details = Comcheckdetail::where('checkId', $check['id'])->get()->toArray();
        $check_details = array_column($check_details, NULL, 'id');
        $company = Company::findOrFail($check['companyId']);
        /*获取复查*/
        $check_again = Comcheckagain::where('checkId', $checkId)->orderBy('checkTime','DESC')->first();
        $check_again = json_decode(json_encode($check_again), true);
        $check_again['words'] = explode(',', $check_again['wordsUrl']);
        $check_again['checkTime'] = date('Y-m-d H:i:s', $check_again['checkTime']);
        /*检查人*/
        $user = User::findOrFail($check_again['userId']);

        /*复查详情*/
        $check_again_details = Comcheckagaindetail::where('checkId', $check_again['id'])->get();

        /*获取所有负面清单*/
        $check_negatives = Checknegative::get()->toArray();
        $check_negatives = array_column($check_negatives, NULL, 'id');

        $reform_status = 1;/*整改状态*/
        $all_reform = "";$all_reform_num = 1;
        $part_reform = "";$part_reform_num = 1;
        $not_reform = "";$not_reform_num = 1;
        $new_danger = "";$new_danger_num = 1;
        foreach($check_again_details as $k=>$v) {
            $content = '';
            if(!empty($v['negatives'])) {
                $negativesId = explode(',',$v['negatives']);
                foreach($negativesId as $kk=>$vv) {
                    $content .= ';'.$check_negatives[$vv]['content'];
                } 
            }
            if(!empty($check_details[$v['checkDetailId']]['content'])) {
                $content .= ';'.$check_details[$v['checkDetailId']]['content'];
            }

            /*新增隐患*/
            $content .= ';'.$v['entryContent'];
            $content = trim($content,';');
            $check_again_details[$k]['check_content'] = $content;

            if($v['status']==1||$v['status']==2) {
                $reform_status = 0;
            }

            if($v['status']==0) {
                $new_danger .= "(".$new_danger_num.")".$content;
                $new_danger_num++;
            }

            if($v['status']==1) {
                $not_reform .= "(".$not_reform_num.")".$content;
                $not_reform_num++;
            }

            if($v['status']==2) {
                $part_reform .= "(".$part_reform_num.")".$content;
                $part_reform_num++;
            }

            if($v['status']==3) {
                $all_reform .= "(".$all_reform_num.")".$content;
                $all_reform_num++;
            }
        }
        
        return view('admin/work/reform',[
            'company' => $company,
            'check_again' => $check_again,
            'user' => $user,
            'reform_status' => $reform_status,
            'new_danger' => $new_danger,
            'not_reform' => $not_reform,
            'part_reform' => $part_reform,
            'all_reform' => $all_reform
        ]);
    }
    //自查列表
    public function selfentrylist(Request $quest)
    {
        $cid = $quest->input("id");
        $data_user = User::with('company')->where('companyId', $cid)->select(array('id','name','companyId'))->first();
        $comName = '';
        if($data_user){
            $comName = $data_user['company']['comName'];
        }
        return view('admin/com/selfentrylist')
            ->with('comName',$comName)
            ->with('cid',$cid);;
    }

    /**
     * 自查列表数据处理
     * @param Request $quest
     * @return mixed
     */
    public function selfentrylistdata(Request $request)
    {

        $cid = $request->input("id");
        $data_user = User::with('company')->where('companyId', $cid)->select(array('id','name','companyId'))->first();
        $res = ['total'=>0,'data'=>[]];
        if($data_user){
            $data_user = $data_user->toArray();
            $query=Comcheckself::where('userId',$data_user['id'])->orderby('id','desc');
            $res=$query->paginate($request->get('limit',15))->toArray();
            foreach ($res['data'] as $key=>$val){
                $urlname = $val['wordsUrl'] ? explode('/',$val['wordsUrl']) : '';
                $res['data'][$key]['urlname'] = $urlname[4]? trim($urlname[4],',.') :'';
                $wordsUrl = $val['wordsUrl'] ? explode(',',$val['wordsUrl']) : '';
                $res['data'][$key]['wordsUrl'] =$wordsUrl[1]? trim($wordsUrl[1],'.') :'';
            }
        }

        $data = [
            'code' => 0,
            'msg'   => '请求成功',
            'count' => $res['total'],
            'data'  => $res['data']
        ];
        return response()->json($data);
    }

    /**
     * 自查照片列表
     */
    public function selfentryimages(Request $quest)
    {
        $cid = $quest->input("cid");
        //$images = ImageNew::where("cid",$cid)->get()->toarray();
        $images = Selfentry::select('image_url as url')->where("selfid",$cid)->whereNotNull('image_url')->get()->toarray();
        return view('admin/work/selfentryimages')->with('images',$images);;
    }

}
